﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SQLite;
using System.IO;
using System.Data;

namespace ServerRPG
{
    static class DBManager
    {
        private static string _path = "DB"; // default
        public static string Path { set { _path = value; } get { return _path; } }
        private static string _name = "main.db"; // default
        public static string Name { set { _name = value; } get { return _name; } }

        /// <summary>
        /// Создает новую базу данных.
        /// </summary>
        /// <returns></returns>
        public static bool Create()
        {
            if (Create(_path, _name))
                return true;
            else
                return false;
        }
        /// <summary>
        /// Создает новую базу данных.
        /// </summary>
        /// <param name="path"></param>
        /// <returns></returns>
        public static bool Create(string path, string name)
        {
            try
            {
                if (!Directory.Exists(path))
                    Directory.CreateDirectory(path);
                SQLiteConnection.CreateFile(path + name);

                string constring = "Data Source=" + _path + _name + ";";
                using (SQLiteConnection sqlco = new SQLiteConnection(constring))
                {
                    string tAccounts = @"CREATE TABLE 'Accounts' (
                            'id'  INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
                            'username'  TEXT NOT NULL,
                            'password'  TEXT NOT NULL,
                            'level'  INTEGER NOT NULL,
                            'regdate'  TEXT NOT NULL,
                            'logindate'  TEXT
                        );
                        CREATE TABLE 'Characters' (
                            'username'  TEXT NOT NULL,
                            'x'  INTEGER,
                            'y'  INTEGER,
                            'zoneID'  INTEGER
                        );
                        CREATE TABLE 'Creatures' (
                            'id'  INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
                            'name'  TEXT NOT NULL
                        );
                        CREATE TABLE 'SpawnPoints' (
                            'creatureID'  INTEGER NOT NULL,
                            'zoneID'  INTEGER NOT NULL,
                            'x'  INTEGER NOT NULL,
                            'y'  INTEGER NOT NULL,
                            'spawnTime'  INTEGER NOT NULL
                        );
                        CREATE TABLE 'Zones' (
                            'id'  INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
                            'name'  TEXT
                        );";
                    SQLiteCommand cmd = new SQLiteCommand(tAccounts, sqlco);
                    sqlco.Open();
                    cmd.ExecuteNonQuery();
                    sqlco.Close();
                }
            }
            catch (Exception ex)
            {
                LogManager.WriteExt(ex.Message);
                return false;
            }
            return true;
        }
        /// <summary>
        /// Использовать для комманд типа Insert или Update.
        /// </summary>
        /// <param name="command"></param>
        /// <returns></returns>
        public static bool ExecuteCommand(string command)
        {
            string constring = "Data Source=" + _path + _name + ";";
            using (SQLiteConnection sqlco = new SQLiteConnection(constring))
            {
                SQLiteCommand cmd = new SQLiteCommand(command, sqlco);
                try
                {
                    sqlco.Open();
                    cmd.ExecuteNonQuery();
                    sqlco.Close();
                }
                catch (Exception ex)
                {
                    LogManager.WriteExt(ex.Message);
                    return false;
                }
            }
            return true;
        }
        /// <summary>
        /// Использовать для комманды Select,
        /// </summary>
        /// <param name="select"></param>
        /// <returns>Возвращает заполненную таблицу.</returns>
        public static DataTable Select(string select)
        {
            string constring = "Data Source=" + _path + _name + ";";
            SQLiteDataAdapter sqlda = new SQLiteDataAdapter(select, constring);
            DataTable dt = new DataTable();
            try
            {
                sqlda.Fill(dt);
            }
            catch (Exception ex)
            {
                LogManager.WriteExt(ex.Message);
            }
            return dt;
        }
        /// <summary>
        /// Импорт sql-скриптов.
        /// </summary>
        /// <param name="filename">Имя файла</param>
        /// <returns>Возвращает удачку.</returns>
        public static bool Import(string filename)
        {
            string sql = "";
            try
            {
                StreamReader sr = new StreamReader(filename);
                sql = sr.ReadToEnd();
                sr.Close();

                ExecuteCommand(sql);
            }
            catch (Exception ex)
            {
                LogManager.WriteExt(ex.Message);
                return false;
            }
            return true;
        }
    }
}
